package com.yangyang.autobackup;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JdbcDbUtil {

    private static final String DB_URL = "jdbc:sqlite:files.db";

    // 获取数据库连接
    public static Connection connect() throws SQLException {
        return DriverManager.getConnection(DB_URL);
    }

    // 创建文件信息表
    public static void createTable() throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS files (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "remotePath TEXT NOT NULL," +
                "localPath TEXT NOT NULL)";
        try (Connection conn = connect();
             Statement stmt = conn.createStatement()) {
            stmt.execute(sql);
        }
    }

    // 插入文件信息到数据库
    public static void insertFile(String remotePath, String localPath) throws SQLException {
        String sql = "INSERT INTO files(remotePath, localPath) VALUES(?, ?)";
        try (Connection conn = connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, remotePath);
            pstmt.setString(2, localPath);
            pstmt.executeUpdate();
        }
    }

    // 查询数据库中的所有文件信息
    public static List<String[]> getAllFiles() throws SQLException {
        String sql = "SELECT remotePath, localPath FROM files";
        List<String[]> files = new ArrayList<>();
        try (Connection conn = connect();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                files.add(new String[]{rs.getString("remotePath"), rs.getString("localPath")});
            }
        }
        return files;
    }

    // 删除数据库中的所有文件记录
    public static void clearFiles() throws SQLException {
        String sql = "DELETE FROM files";
        try (Connection conn = connect();
             Statement stmt = conn.createStatement()) {
            stmt.executeUpdate(sql);
        }
    }

    // 关闭数据库连接
    public static void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
